"""
评估 CRUD 操作
"""
from typing import Optional, List
from uuid import UUID
from sqlalchemy import select, and_, func, desc
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import selectinload
from app.models.assessment import Assessment, AssessmentIssue
from app.schemas.assessment import AssessmentCreate, AssessmentIssueCreate
from app.core.logger import app_logger


class AssessmentCRUD:
    """评估 CRUD 操作类"""
    
    @staticmethod
    async def create(
        db: AsyncSession,
        assessment_data: dict,
        issues_data: List[dict] = None
    ) -> Assessment:
        """
        创建评估记录
        
        Args:
            db: 数据库会话
            assessment_data: 评估数据
            issues_data: 问题列表
        """
        try:
            # 创建评估主记录
            db_assessment = Assessment(**assessment_data)
            db.add(db_assessment)
            await db.flush()
            
            # 创建问题记录
            if issues_data:
                for issue_data in issues_data:
                    issue = AssessmentIssue(
                        assessment_id=db_assessment.id,
                        **issue_data
                    )
                    db.add(issue)
            
            await db.commit()
            await db.refresh(db_assessment)
            
            app_logger.info(
                f"创建评估成功: ID={db_assessment.id}, "
                f"病历ID={db_assessment.record_id}, "
                f"得分={db_assessment.overall_score}"
            )
            return db_assessment
            
        except Exception as e:
            await db.rollback()
            app_logger.error(f"创建评估失败: {e}")
            raise
    
    @staticmethod
    async def get_by_id(db: AsyncSession, assessment_id: UUID) -> Optional[Assessment]:
        """
        根据ID获取评估
        """
        stmt = (
            select(Assessment)
            .where(Assessment.id == assessment_id)
            .options(
                selectinload(Assessment.issues),
                selectinload(Assessment.record)
            )
        )
        result = await db.execute(stmt)
        return result.scalar_one_or_none()
    
    @staticmethod
    async def update(
        db: AsyncSession,
        assessment_id: UUID,
        assessment_data: dict,
        issues_data: List[dict] = None
    ) -> Assessment:
        """
        更新评估记录
        
        Args:
            db: 数据库会话
            assessment_id: 评估ID
            assessment_data: 更新的评估数据
            issues_data: 问题列表（会删除旧问题并创建新问题）
        """
        try:
            # 获取现有评估
            assessment = await AssessmentCRUD.get_by_id(db, assessment_id)
            if not assessment:
                raise ValueError(f"评估记录不存在: {assessment_id}")
            
            # 更新评估主记录
            for key, value in assessment_data.items():
                setattr(assessment, key, value)
            
            # 更新 assessed_at 时间戳
            from datetime import datetime
            assessment.assessed_at = datetime.now()
            
            # 删除旧问题
            stmt = select(AssessmentIssue).where(AssessmentIssue.assessment_id == assessment_id)
            result = await db.execute(stmt)
            old_issues = result.scalars().all()
            for issue in old_issues:
                await db.delete(issue)
            
            # 创建新问题记录
            if issues_data:
                for issue_data in issues_data:
                    issue = AssessmentIssue(
                        assessment_id=assessment_id,
                        **issue_data
                    )
                    db.add(issue)
            
            await db.commit()
            await db.refresh(assessment)
            
            app_logger.info(
                f"更新评估成功: ID={assessment_id}, "
                f"病历ID={assessment.record_id}, "
                f"得分={assessment.overall_score}"
            )
            return assessment
            
        except Exception as e:
            await db.rollback()
            app_logger.error(f"更新评估失败: {e}")
            raise
    
    @staticmethod
    async def get_by_record_id(
        db: AsyncSession,
        record_id: UUID,
        latest_only: bool = True
    ) -> Optional[Assessment] | List[Assessment]:
        """
        根据病历ID获取评估
        
        Args:
            record_id: 病历ID
            latest_only: 是否只返回最新的评估
        """
        stmt = (
            select(Assessment)
            .where(Assessment.record_id == record_id)
            .options(selectinload(Assessment.issues))
            .order_by(desc(Assessment.assessed_at))
        )
        
        if latest_only:
            result = await db.execute(stmt.limit(1))
            return result.scalar_one_or_none()
        else:
            result = await db.execute(stmt)
            return list(result.scalars().all())
    
    @staticmethod
    async def get_list(
        db: AsyncSession,
        page: int = 1,
        page_size: int = 10
    ) -> tuple[List[Assessment], int]:
        """
        获取评估列表
        
        Returns:
            (评估列表, 总数)
        """
        # 查询总数
        count_stmt = select(func.count()).select_from(Assessment)
        total_result = await db.execute(count_stmt)
        total = total_result.scalar()
        
        # 查询列表
        stmt = (
            select(Assessment)
            .order_by(desc(Assessment.assessed_at))
            .offset((page - 1) * page_size)
            .limit(page_size)
            .options(selectinload(Assessment.record))
        )
        
        result = await db.execute(stmt)
        assessments = result.scalars().all()
        
        return list(assessments), total
    
    @staticmethod
    async def get_statistics(db: AsyncSession) -> dict:
        """
        获取评估统计信息
        """
        # 总评估数
        total_stmt = select(func.count()).select_from(Assessment)
        total_result = await db.execute(total_stmt)
        total = total_result.scalar()
        
        # 平均分
        avg_stmt = select(func.avg(Assessment.overall_score))
        avg_result = await db.execute(avg_stmt)
        avg_score = avg_result.scalar() or 0.0
        
        # 分数分布（按10分一档）
        score_distribution = {}
        for i in range(0, 10):
            min_score = i * 10
            max_score = (i + 1) * 10
            count_stmt = (
                select(func.count())
                .select_from(Assessment)
                .where(
                    and_(
                        Assessment.overall_score >= min_score,
                        Assessment.overall_score < max_score if i < 9 else Assessment.overall_score <= max_score
                    )
                )
            )
            count_result = await db.execute(count_stmt)
            count = count_result.scalar()
            score_distribution[f"{min_score}-{max_score}"] = count
        
        # 常见问题统计（按问题类型）
        issues_stmt = (
            select(
                AssessmentIssue.issue_type,
                func.count(AssessmentIssue.id).label('count')
            )
            .group_by(AssessmentIssue.issue_type)
            .order_by(desc('count'))
            .limit(5)
        )
        issues_result = await db.execute(issues_stmt)
        common_issues = [
            {'type': row[0], 'count': row[1]}
            for row in issues_result.all()
        ]
        
        return {
            'total_assessments': total,
            'average_score': round(float(avg_score), 2),
            'score_distribution': score_distribution,
            'common_issues': common_issues
        }
    
    @staticmethod
    async def delete(db: AsyncSession, assessment_id: UUID) -> bool:
        """
        删除评估记录（包括关联的问题）
        """
        try:
            assessment = await AssessmentCRUD.get_by_id(db, assessment_id)
            if not assessment:
                return False
            
            await db.delete(assessment)
            await db.commit()
            
            app_logger.info(f"删除评估成功: ID={assessment_id}")
            return True
            
        except Exception as e:
            await db.rollback()
            app_logger.error(f"删除评估失败: {e}")
            raise

